﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ReportNalogObject
    {
        public const string sql = @"
SELECT 'Обороты по безналу:' as Описание, '' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryProject.Name as Проект, convert(nchar(20),TotalNalog.Summa) as Сумма
FROM (
   select Project, sum(Summa) as Summa
   from Transactions 
   where Type = 1 and Currency in (select convert(int,value) from Settings where Code Like 'code_currency%')
   group by Project) as TotalNalog
LEFT JOIN DictionaryProject on TotalNalog.Project = DictionaryProject.ID
UNION ALL
SELECT 'Начислено налогов:' as Описание, '' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryProject.Name as Проект, convert(nchar(20),TotalNalog.Summa) as Сумма
FROM (
   select Project, sum(Summa)/20 as Summa
   from Transactions 
   where Type = 1 and Currency in (select convert(int,value) from Settings where Code Like 'code_currency%')
   group by Project) as TotalNalog
LEFT JOIN DictionaryProject on TotalNalog.Project = DictionaryProject.ID
UNION ALL
SELECT 'Выплачено налогов:' as Описание, '' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryProject.Name as Проект, convert(nchar(20),TotalOtdano.Summa) as Сумма
FROM (
   select Project, sum(Summa) as Summa
   from Transactions 
   where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_nalog%')
   group by Project) as TotalOtdano
LEFT JOIN DictionaryProject on TotalOtdano.Project = DictionaryProject.ID
UNION ALL
SELECT 'Осталося уплатить:' as Описание, '' as Проект, '' as Сумма
UNION ALL
SELECT '' as Описание, DictionaryProject.Name as Проект, convert(nchar(20),sum(raznitsa1.Summa)) as Сумма
FROM (
   select Project, sum(Summa)/20 as Summa from Transactions 
   where Type = 1 and Currency in (select convert(int,value) 
   from Settings where Code Like 'code_currency%')
   group by Project
   union all
   select Project, -sum(Summa) as Summa
   from Transactions 
   where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_nalog%')
   group by Project) as raznitsa1
LEFT JOIN DictionaryProject on raznitsa1.Project = DictionaryProject.ID
GROUP BY DictionaryProject.Name
UNION ALL
SELECT '' as Описание, '' as Проект, '' as Сумма
UNION ALL
select 'Всего оборотов по безналу:' as Описание, 'Все проекты' as Проект, convert(nchar(20),sum(Summa)) as Сумма 
from Transactions
where Type = 1 and Currency in (select convert(int,value) from Settings where Code Like 'code_currency%')
UNION ALL
select 'Всего начислено налога:' as Описание, 'Все проекты' as Проект, convert(nchar(20),sum(Summa)/20) as Сумма 
from Transactions
where Type = 1 and Currency in (select convert(int,value) from Settings where Code Like 'code_currency%')
UNION ALL
select 'Всего выплачено налога:' as Описание, 'Все проекты' as Проект, convert(nchar(20),sum(Summa)) as Сумма 
from Transactions
where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_nalog%')
UNION ALL
SELECT 'Всего осталось уплатить:' as Описание, 'Все проекты' as Проект, convert(nchar(20),sum(raznitsa2.Summa)) as Сумма 
FROM
   (select sum(Summa)/20 as Summa 
   from Transactions 
   where Type = 1 and Currency in (select convert(int,value) from Settings where Code Like 'code_currency%')
   union all
   select -sum(Summa) as Summa
   from Transactions
   where Type = 2 and Category in (select convert(int,value) from Settings where Code Like 'code_nalog%')) as raznitsa2
";

        public string Описание { get; set; }
        public string Проект { get; set; }
        public string Сумма { get; set; }
    }
}
